/* 
*	File: examples.sql
*	Fills a (clean) database with some data examples.
*     Aims to have some data in all the tables.
*	 By Jack Wu, Jessica Wong, Vivianne Barzu, Serge Okon.
*/

-- Borrower Examples
INSERT INTO Borrower VALUES (seqBid.nextval, 'sergepass', 'Serge Okon', '12 1st Street', '604-123-4567', 'serge@place.com', 12345, '25-DEC-2011', 'Student');
INSERT INTO Borrower VALUES (seqBid.nextval, 'jackpass', 'Jack Wu', '10 1st Street', '604-123-5678', 'jack@place.com', 12346, '25-DEC-2012', 'Faculty');
INSERT INTO Borrower VALUES (seqBid.nextval, 'jesspass', 'Jess Wong', '15 1st Street', '604-123-6789', 'jess@place.com', 12347, '25-DEC-2013', 'Staff');
INSERT INTO Borrower VALUES (seqBid.nextval, 'vivianne', 'Vivianne Barzu', '21 1st Street', '604-123-7890', 'vivianne@place.com', 12348, '25-DEC-2014', 'Student');

-- Borrower Type Examples
/* (none needed, the ones for this project are already defined in create_db.sql */

-- Book Examples - We'll group these by book
-- A book with lots of copies, none out now, but lots of history
INSERT INTO Book VALUES ('Q 0-123-45678-1 1884', '0-123-45678-1', 'Adventures of Huckleberry Finn', 'Mark Twain', 'Classic sHouse', 1884);
INSERT INTO HasAuthor VALUES('Q 0-123-45678-1 1884', 'Mark Twain');
INSERT INTO HasSubject VALUES('Q 0-123-45678-1 1884', 'Classics');
INSERT INTO BookCopy VALUES('Q 0-123-45678-1 1884', 1, 'In');
INSERT INTO BookCopy VALUES('Q 0-123-45678-1 1884', 2, 'In');
INSERT INTO BookCopy VALUES('Q 0-123-45678-1 1884', 3, 'In');
INSERT INTO BookCopy VALUES('Q 0-123-45678-1 1884', 4, 'In');
INSERT INTO BookCopy VALUES('Q 0-123-45678-1 1884', 5, 'In');
INSERT INTO Borrowing VALUES (seqBorid.nextval, 1, 'Q 0-123-45678-1 1884', 1, '01-JAN-2009', '05-JAN-2009');
INSERT INTO Borrowing VALUES (seqBorid.nextval, 2, 'Q 0-123-45678-1 1884', 1, '10-JAN-2009', '15-JAN-2009');
INSERT INTO Borrowing VALUES (seqBorid.nextval, 1, 'Q 0-123-45678-1 1884', 2, '12-JAN-2009', '16-JAN-2009');
INSERT INTO Borrowing VALUES (seqBorid.nextval, 1, 'Q 0-123-45678-1 1884', 2, '01-SEP-2011', '10-NOV-2011');
-- a very long borrowing - fine time
INSERT INTO Fine VALUES (seqFid.nextval, 12, '10-NOV-2011', null, seqBorid.currval); 

-- A book with all it's copies out by borrowers
INSERT INTO Book VALUES ('Q 0-123-56789-1 1812', '0-123-56789-1', 'Grimms Fairy Tales', 'Jacob Grimm', 'Classics House', 1812);
INSERT INTO HasAuthor VALUES('Q 0-123-56789-1 1812', 'Jacob Grimm');
INSERT INTO HasAuthor VALUES('Q 0-123-56789-1 1812', 'Wilhelm Grimm');
INSERT INTO HasSubject VALUES('Q 0-123-56789-1 1812', 'Classics');
INSERT INTO BookCopy VALUES('Q 0-123-56789-1 1812', 1, 'Out');
INSERT INTO BookCopy VALUES('Q 0-123-56789-1 1812', 2, 'Out');
INSERT INTO BookCopy VALUES('Q 0-123-56789-1 1812', 3, 'Out');
INSERT INTO Borrowing VALUES (seqBorid.nextval, 1, 'Q 0-123-56789-1 1812', 1, '20-NOV-2011', null);
INSERT INTO Borrowing VALUES (seqBorid.nextval, 2, 'Q 0-123-56789-1 1812', 2, '18-NOV-2011', null);
INSERT INTO Borrowing VALUES (seqBorid.nextval, 3, 'Q 0-123-56789-1 1812', 3, '19-NOV-2011', null);


-- A book with 2 copies, 1 is in, 1 is out
INSERT INTO Book VALUES ('Q 0-234-56889-1 1813', '0-234-56889-1', 'Pride and Prejudice', 'Jane Austen', 'Classics House', 1813);
INSERT INTO HasAuthor VALUES('Q 0-234-56889-1 1813', 'Jane Austen');
INSERT INTO HasSubject VALUES('Q 0-234-56889-1 1813', 'Classics');
INSERT INTO BookCopy VALUES('Q 0-234-56889-1 1813', 1, 'In');
INSERT INTO BookCopy VALUES('Q 0-234-56889-1 1813', 2, 'Out');
INSERT INTO Borrowing VALUES (seqBorid.nextval, 4, 'Q 0-234-56889-1 1813', 2, '20-NOV-2011', null);

-- A book that has 1 of its copies out, and 1 ready to pick up.
INSERT INTO Book VALUES ('Q 0-456-23789-2 2005', '0-456-23789-2', 'Comedy Writing Secrets', 'Mel Helitzer', 'Writer Digest Books', 2005);
INSERT INTO HasAuthor VALUES('Q 0-456-23789-2 2005', 'Mel Helitzer');
INSERT INTO HasAuthor VALUES('Q 0-456-23789-2 2005', 'Mark Shatz');
INSERT INTO HasSubject VALUES('Q 0-456-23789-2 2005', 'Comedy');
INSERT INTO BookCopy VALUES('Q 0-456-23789-2 2005', 1, 'Out');
INSERT INTO BookCopy VALUES('Q 0-456-23789-2 2005', 2, 'On-hold');
-- Add a hold request to Serge for this book - at desk on 12-Dec-2011
INSERT INTO HoldRequest VALUES (seqHid.nextval, 1, 'Q 0-456-23789-2 2005', '12-DEC-2011');
INSERT INTO Borrowing VALUES (seqBorid.nextval, 4, 'Q 0-456-23789-2 2005', 1, '20-NOV-2011', null);

-- A book with 1 copy out, rest are in.
INSERT INTO Book VALUES ('Q 0-471-45249-1 2004', '0-471-45249-1', 'Operating System Concepts with Java', 'Abraham Silberschatz', 'John Willey and Sons', 2004);
INSERT INTO HasAuthor VALUES('Q 0-471-45249-1 2004', 'Abraham Silberschatz');
INSERT INTO HasAuthor VALUES('Q 0-471-45249-1 2004', 'Peter Baer Galvin');
INSERT INTO HasAuthor VALUES('Q 0-471-45249-1 2004', 'Greg Gagne');
INSERT INTO HasSubject VALUES('Q 0-471-45249-1 2004', 'Computers');
INSERT INTO BookCopy VALUES('Q 0-471-45249-1 2004', 1, 'In');
INSERT INTO BookCopy VALUES('Q 0-471-45249-1 2004', 2, 'Out');
INSERT INTO BookCopy VALUES('Q 0-471-45249-1 2004', 3, 'In');
INSERT INTO Borrowing VALUES (seqBorid.nextval, 4, 'Q 0-471-45249-1 2004', 2, '20-NOV-2011', null);